package com.hujie.sqlqueryes.service.impl;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.schema.Column;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;

/**
 * 查询表达式构建器实现类
 */
@Service
public class QueryExpressionBuilderImpl {
    
    private static final Logger logger = LoggerFactory.getLogger(QueryExpressionBuilderImpl.class);
    
    /**
     * 根据表达式构建查询
     * @param expression 表达式
     * @return 查询DSL字符串
     */
    public String buildQueryFromExpression(Expression expression) {
        logger.debug("开始构建查询表达式: {}", expression);
        
        if (expression instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) expression;
            return buildQueryFromExpression(parenthesis.getExpression());
        } else if (expression instanceof AndExpression) {
            AndExpression andExpr = (AndExpression) expression;
            return buildAndQuery(andExpr);
        } else if (expression instanceof OrExpression) {
            OrExpression orExpr = (OrExpression) expression;
            return buildOrQuery(orExpr);
        } else if (expression instanceof EqualsTo) {
            EqualsTo equalsTo = (EqualsTo) expression;
            return buildEqualsQuery(equalsTo);
        } else if (expression instanceof NotEqualsTo) {
            NotEqualsTo notEqualsTo = (NotEqualsTo) expression;
            return buildNotEqualsQuery(notEqualsTo);
        } else if (expression instanceof GreaterThan) {
            GreaterThan greaterThan = (GreaterThan) expression;
            return buildGreaterThanQuery(greaterThan);
        } else if (expression instanceof GreaterThanEquals) {
            GreaterThanEquals greaterThanEquals = (GreaterThanEquals) expression;
            return buildGreaterThanOrEqualQuery(greaterThanEquals);
        } else if (expression instanceof MinorThan) {
            MinorThan minorThan = (MinorThan) expression;
            return buildLessThanQuery(minorThan);
        } else if (expression instanceof MinorThanEquals) {
            MinorThanEquals minorThanEquals = (MinorThanEquals) expression;
            return buildLessThanOrEqualQuery(minorThanEquals);
        } else if (expression instanceof LikeExpression) {
            LikeExpression likeExpression = (LikeExpression) expression;
            return buildLikeQuery(likeExpression);
        } else if (expression instanceof InExpression) {
            InExpression inExpression = (InExpression) expression;
            return buildInQuery(inExpression);
        }
        
        // 默认情况返回match_all
        logger.warn("未知的表达式类型，返回match_all查询");
        return "{\n    \"match_all\": {}\n  }";
    }
    
    /**
     * 构建AND查询
     * @param andExpr AND表达式
     * @return 查询DSL字符串
     */
    private String buildAndQuery(AndExpression andExpr) {
        StringBuilder query = new StringBuilder();
        query.append("{\n    \"bool\": {\n      \"must\": [\n");
        
        // 处理左侧表达式
        query.append("        ").append(buildQueryFromExpression(andExpr.getLeftExpression())).append(",\n");
        
        // 处理右侧表达式
        query.append("        ").append(buildQueryFromExpression(andExpr.getRightExpression())).append("\n");
        
        query.append("      ]\n    }\n  }");
        return query.toString();
    }
    
    /**
     * 构建OR查询
     * @param orExpr OR表达式
     * @return 查询DSL字符串
     */
    private String buildOrQuery(OrExpression orExpr) {
        StringBuilder query = new StringBuilder();
        query.append("{\n    \"bool\": {\n      \"should\": [\n");
        
        // 处理左侧表达式
        query.append("        ").append(buildQueryFromExpression(orExpr.getLeftExpression())).append(",\n");
        
        // 处理右侧表达式
        query.append("        ").append(buildQueryFromExpression(orExpr.getRightExpression())).append("\n");
        
        query.append("      ],\n      \"minimum_should_match\": 1\n    }\n  }");
        return query.toString();
    }
    
    /**
     * 构建等于查询
     * @param equalsTo 等于表达式
     * @return 查询DSL字符串
     */
    private String buildEqualsQuery(EqualsTo equalsTo) {
        Column column = (Column) equalsTo.getLeftExpression();
        Expression rightExpression = equalsTo.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 对于所有字段，使用term查询进行精确匹配
        if (isNumeric(fieldValue)) {
            return "{\n      \"term\": {\n        \"" + fieldName + "\": " + fieldValue + "\n      }\n    }";
        } else {
            // 移除可能存在的多余引号
            String cleanValue = fieldValue.replaceAll("^\"|\"$", "").replaceAll("^'|'$", "");
            return "{\n      \"term\": {\n        \"" + fieldName + "\": \"" + escapeJsonString(cleanValue) + "\"\n      }\n    }";
        }
    }
    
    /**
     * 构建不等于查询
     * @param notEqualsTo 不等于表达式
     * @return 查询DSL字符串
     */
    private String buildNotEqualsQuery(NotEqualsTo notEqualsTo) {
        Column column = (Column) notEqualsTo.getLeftExpression();
        Expression rightExpression = notEqualsTo.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 对于所有字段，使用term查询进行精确匹配
        StringBuilder query = new StringBuilder();
        query.append("{\n      \"bool\": {\n");
        query.append("        \"must_not\": [\n");
        if (isNumeric(fieldValue)) {
            query.append("          {\"term\": {\"" + fieldName + "\": " + fieldValue + "}}\n");
        } else {
            // 移除可能存在的多余引号
            String cleanValue = fieldValue.replaceAll("^\"|\"$", "").replaceAll("^'|'$", "");
            query.append("          {\"term\": {\"" + fieldName + "\": \"" + escapeJsonString(cleanValue) + "\"}}\n");
        }
        query.append("        ]\n");
        query.append("      }\n    }");
        
        return query.toString();
    }
    
    /**
     * 构建大于查询
     * @param greaterThan 大于表达式
     * @return 查询DSL字符串
     */
    private String buildGreaterThanQuery(GreaterThan greaterThan) {
        Column column = (Column) greaterThan.getLeftExpression();
        Expression rightExpression = greaterThan.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 尝试判断是否为数字类型
        if (isNumeric(fieldValue)) {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"gt\": " + fieldValue + "\n        }\n      }\n    }";
        } else {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"gt\": \"" + escapeJsonString(fieldValue) + "\"\n        }\n      }\n    }";
        }
    }
    
    /**
     * 构建大于等于查询
     * @param greaterThanEquals 大于等于表达式
     * @return 查询DSL字符串
     */
    private String buildGreaterThanOrEqualQuery(GreaterThanEquals greaterThanEquals) {
        Column column = (Column) greaterThanEquals.getLeftExpression();
        Expression rightExpression = greaterThanEquals.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 尝试判断是否为数字类型
        if (isNumeric(fieldValue)) {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"gte\": " + fieldValue + "\n        }\n      }\n    }";
        } else {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"gte\": \"" + escapeJsonString(fieldValue) + "\"\n        }\n      }\n    }";
        }
    }
    
    /**
     * 构建小于查询
     * @param minorThan 小于表达式
     * @return 查询DSL字符串
     */
    private String buildLessThanQuery(MinorThan minorThan) {
        Column column = (Column) minorThan.getLeftExpression();
        Expression rightExpression = minorThan.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 尝试判断是否为数字类型
        if (isNumeric(fieldValue)) {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"lt\": " + fieldValue + "\n        }\n      }\n    }";
        } else {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"lt\": \"" + escapeJsonString(fieldValue) + "\"\n        }\n      }\n    }";
        }
    }
    
    /**
     * 构建小于等于查询
     * @param minorThanEquals 小于等于表达式
     * @return 查询DSL字符串
     */
    private String buildLessThanOrEqualQuery(MinorThanEquals minorThanEquals) {
        Column column = (Column) minorThanEquals.getLeftExpression();
        Expression rightExpression = minorThanEquals.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 尝试判断是否为数字类型
        if (isNumeric(fieldValue)) {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"lte\": " + fieldValue + "\n        }\n      }\n    }";
        } else {
            return "{\n      \"range\": {\n        \"" + fieldName + "\": {\n          \"lte\": \"" + escapeJsonString(fieldValue) + "\"\n        }\n      }\n    }";
        }
    }
    
    /**
     * 构建LIKE查询
     * @param likeExpression LIKE表达式
     * @return 查询DSL字符串
     */
    private String buildLikeQuery(LikeExpression likeExpression) {
        Column column = (Column) likeExpression.getLeftExpression();
        Expression rightExpression = likeExpression.getRightExpression();
        
        String fieldName = column.getColumnName();
        String fieldValue = getValueFromExpression(rightExpression);
        
        // 处理LIKE表达式的通配符
        String pattern = fieldValue.replace("%", "*").replace("_", "?");
        
        // 使用wildcard查询并启用case_insensitive选项以提高匹配能力
        return "{\n      \"wildcard\": {\n        \"" + fieldName + "\": {\n          \"value\": \"" + escapeJsonString(pattern) + "\",\n          \"case_insensitive\": true\n        }\n      }\n    }";
    }
    
    /**
     * 构建IN查询
     * @param inExpression IN表达式
     * @return 查询DSL字符串
     */
    private String buildInQuery(InExpression inExpression) {
        Column column = (Column) inExpression.getLeftExpression();
        // 修复ItemsList类型转换
        Expression rightItemsList = inExpression.getRightExpression();
        
        String fieldName = column.getColumnName();
        
        StringBuilder query = new StringBuilder();
        query.append("{\n      \"terms\": {\n");
        query.append("        \"").append(fieldName).append("\": [");
        
        List<String> values = new ArrayList<>();
        
        // 检查rightItemsList是否为ExpressionList类型
        if (rightItemsList instanceof ExpressionList) {
            ExpressionList expressionList = (ExpressionList) rightItemsList;
            List<Expression> expressions = expressionList.getExpressions();
            
            for (Expression expr : expressions) {
                String value = getValueFromExpression(expr);
                if (isNumeric(value)) {
                    values.add(value);
                } else {
                    values.add("\"" + escapeJsonString(value) + "\"");
                }
            }
        }
        
        query.append(String.join(", ", values));
        query.append("]\n      }\n    }");
        
        return query.toString();
    }
    
    /**
     * 从表达式中获取值
     * @param expression 表达式
     * @return 值字符串
     */
    private String getValueFromExpression(Expression expression) {
        if (expression instanceof StringValue) {
            return ((StringValue) expression).getValue();
        } else if (expression instanceof LongValue) {
            return String.valueOf(((LongValue) expression).getValue());
        } else if (expression instanceof DoubleValue) {
            return String.valueOf(((DoubleValue) expression).getValue());
        }
        return expression.toString();
    }
    
    /**
     * 转义JSON字符串中的特殊字符
     * @param str 原始字符串
     * @return 转义后的字符串
     */
    private String escapeJsonString(String str) {
        if (str == null) {
            return null;
        }
        return str.replace("\\", "\\\\")
                  .replace("\"", "\\\"")
                  .replace("\b", "\\b")
                  .replace("\f", "\\f")
                  .replace("\n", "\\n")
                  .replace("\r", "\\r")
                  .replace("\t", "\\t");
    }
    
    /**
     * 判断字符串是否为数字
     * @param str 字符串
     * @return 是否为数字
     */
    private boolean isNumeric(String str) {
        if (str == null || str.isEmpty()) {
            return false;
        }
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
}